package org.skydiveapplibrary.data;

import java.util.Date;
import java.util.UUID;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseUpgrade_1_2
{
	public void doUpgrade(SQLiteDatabase db)
	{
		// alter tables
		db.execSQL("ALTER TABLE log_entry ADD COLUMN last_modified_utc text");
		db.execSQL("ALTER TABLE log_entry ADD COLUMN last_signature_utc text");
		db.execSQL("ALTER TABLE log_entry ADD COLUMN unique_id text");
		db.execSQL("ALTER TABLE skydive_type ADD COLUMN last_modified_utc text");
		db.execSQL("ALTER TABLE skydive_type ADD COLUMN unique_id text");
		db.execSQL("ALTER TABLE location ADD COLUMN last_modified_utc text");
		db.execSQL("ALTER TABLE location ADD COLUMN unique_id text");
		db.execSQL("ALTER TABLE aircraft ADD COLUMN last_modified_utc text");
		db.execSQL("ALTER TABLE aircraft ADD COLUMN unique_id text");
		db.execSQL("ALTER TABLE rig ADD COLUMN last_modified_utc text");
		db.execSQL("ALTER TABLE rig ADD COLUMN unique_id text");
		
		// get last modified date
		Date lastModified = new Date(System.currentTimeMillis());
		
		// upgrade data
		upgradeLogEntries(db, lastModified);
		updateLogEntrySignatures(db, lastModified);
		upgradeAircrafts(db, lastModified);
		upgradeLocations(db, lastModified);
		upgradeRigs(db, lastModified);
		upgradeSkydiveTypes(db, lastModified);
	}
	
	private void upgradeLogEntries(SQLiteDatabase db, Date lastModified)
	{
		Cursor cursor = null;

		// Update LOG_ENTRY_TABLE
		try
		{
			String query = "SELECT " + Database.LOG_ENTRY_ID + " FROM "
					+ Database.LOG_ENTRY_TABLE;
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.LOG_ENTRY_UNIQUE_ID, UUID.randomUUID().toString());
					values.put(Database.LOG_ENTRY_LAST_MODIFIED_UTC, Database.dateTimeToString(lastModified));

					db.update(
							Database.LOG_ENTRY_TABLE,
							values,
							Database.LOG_ENTRY_ID + " = "
									+ Database.getInteger(cursor, Database.LOG_ENTRY_ID),
							null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void updateLogEntrySignatures(SQLiteDatabase db, Date lastSignatureDate)
	{
		Cursor cursor = null;

		// Update LOG_ENTRY_TABLE
		try
		{
			String query = "SELECT " + Database.LOG_ENTRY_ID + " FROM "
					+ Database.LOG_ENTRY_TABLE +
					" WHERE " + Database.LOG_ENTRY_SIGNATURE_ID + " IS NOT NULL";
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.LOG_ENTRY_LAST_SIGNATURE_UTC, Database.dateTimeToString(lastSignatureDate));

					db.update(
							Database.LOG_ENTRY_TABLE,
							values,
							Database.LOG_ENTRY_ID + " = "
									+ Database.getInteger(cursor, Database.LOG_ENTRY_ID),
							null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void upgradeAircrafts(SQLiteDatabase db, Date lastModified)
	{
		Cursor cursor = null;
		
		// Update AIRCRAFT_TABLE
		try
		{
			String query = "SELECT " + Database.AIRCRAFT_ID + " FROM " + Database.AIRCRAFT_TABLE;
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.AIRCRAFT_UNIQUE_ID, UUID.randomUUID().toString());
					values.put(Database.AIRCRAFT_LAST_MODIFIED_UTC, Database.dateTimeToString(lastModified));

					db.update(Database.AIRCRAFT_TABLE, values, Database.AIRCRAFT_ID + " = "
							+ Database.getInteger(cursor, Database.AIRCRAFT_ID), null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void upgradeLocations(SQLiteDatabase db, Date lastModified)
	{
		Cursor cursor = null;
		
		// Update LOCATION_TABLE
		try
		{
			String query = "SELECT " + Database.LOCATION_ID + " FROM " + Database.LOCATION_TABLE;
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.LOCATION_UNIQUE_ID, UUID.randomUUID().toString());
					values.put(Database.LOCATION_LAST_MODIFIED_UTC, Database.dateTimeToString(lastModified));

					db.update(Database.LOCATION_TABLE, values, Database.LOCATION_ID + " = "
							+ Database.getInteger(cursor, Database.LOCATION_ID), null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void upgradeRigs(SQLiteDatabase db, Date lastModified)
	{
		Cursor cursor = null;
		
		// Update RIG_TABLE
		try
		{
			String query = "SELECT " + Database.RIG_ID + " FROM " + Database.RIG_TABLE;
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.RIG_UNIQUE_ID, UUID.randomUUID().toString());
					values.put(Database.RIG_LAST_MODIFIED_UTC, Database.dateTimeToString(lastModified));

					db.update(Database.RIG_TABLE, values,
							Database.RIG_ID + " = " + Database.getInteger(cursor, Database.RIG_ID), null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void upgradeSkydiveTypes(SQLiteDatabase db, Date lastModified)
	{
		Cursor cursor = null;
		
		// Update SKYDIVE_TYPE_TABLE
		try
		{
			String query = "SELECT " + Database.SKYDIVE_TYPE_ID + " FROM "
					+ Database.SKYDIVE_TYPE_TABLE;
			cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst())
			{
				do
				{
					ContentValues values = new ContentValues();
					values.put(Database.SKYDIVE_TYPE_UNIQUE_ID, UUID.randomUUID().toString());
					values.put(Database.SKYDIVE_TYPE_LAST_MODIFIED_UTC, Database.dateTimeToString(lastModified));

					db.update(Database.SKYDIVE_TYPE_TABLE, values, Database.SKYDIVE_TYPE_ID
							+ " = " + Database.getInteger(cursor, Database.SKYDIVE_TYPE_ID), null);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
}
